package com.util.sql;

import org.omg.CORBA.OBJ_ADAPTER;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 持久层单表操作封装
 *
 * @param <T> 查询对象
 * @param <> 数据表前缀
 */
public class BaseDao<T> {


    /**
     * 根据对象查询
     *
     * @param
     * @param <T>
     * @return
     */
    public <T> T get(T t) {
        //获取Class对象
        Class clazz = t.getClass();
        //获取对象成员的数组
        Field[] fields = clazz.getDeclaredFields();
        //将有值的成员添加进查询参数
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT  TOP 1 * FROM [")
                .append(clazz.getSimpleName())
                .append("]\0 WHERE 1 = 1");
        //参数集合
        ArrayList<Object> paramList = new ArrayList<>();
        try {
            for (Field field : fields) {
                field.setAccessible(true);//跳过语言检查

                if (field.get(t) != null
                        && !field.get(t) .equals("")
                        && !field.get(t).equals(0)
                        && !field.get(t).equals("null")) {


                    sql.append("\0 AND\0")
                            .append(field.getName())
                            .append("=")
                            .append("\'")
                            .append("%s")
                            .append("\'");

                    //添加进集合
                    paramList.add(field.get(t));
                }
                ;

            }
        } catch (Exception e) {
            System.out.println("获取查询对象参数异常：" + e.getLocalizedMessage());
            e.printStackTrace();

        }
        //参数数组
        Object[] param = new Object[paramList.size()];
        for (int i = 0; i < paramList.size(); i++) {
            param[i] = paramList.get(i);
        }
        //执行sql
        List<Map> list = null;
        try {
            list = (List) SqlServerHelper.queryList(sql.toString(), param);
            if (list.size() == 0) {
                return null;
            }
        } catch (Exception e) {
            System.out.println(String.format("执行查询对象SQL失败%s", e.getMessage()));
        }
        Object obj = null;
        try {
            obj = clazz.newInstance();
        } catch (Exception e) {
            System.out.println(String.format("%s查询对象实例化失败：%s", clazz.getName(), e.getMessage()));
        }
        try {
            System.out.println(list);

            for (Field field : fields) {

                field.setAccessible(true);//路过语言查询
                if(null != list.get(0).get(field.getName()  )){
                    field.set(obj, list.get(0).get(field.getName())); //设置成员值
                }
            }
        } catch (Exception e) {
            System.out.println(String.format("%s查询对象设置成员失败：%s", clazz.getName(), e.getMessage()));
            e.printStackTrace();
        }

        return (T) obj;
    }

    /**
     * 根据对象修改
     * 修改逻辑 1.查询出对象 2.在旧对象上修改新对象 ？？如何获取旧对象呢
     *
     * @param
     * @param
     * @return
     */
    public int update(T oldT, T newT) {
        Class oldClazz = oldT.getClass();
        Class newClazz = newT.getClass();
        Field[] oldField = oldClazz.getDeclaredFields();
        Field[] newField = newClazz.getDeclaredFields();
        StringBuilder sql = new StringBuilder();
        sql.append(String.format("UPDATE [%s] SET ", newClazz.getSimpleName()));
        try {
            //获取修改字段
            //获取末尾下标
            //不取第一个自动增长字段
            int newTail = 1;
            for (int i = 1; i < newField.length; i++) {
                newField[i].setAccessible(true);
                if (newField[i].get(newT) != null
                        && newField[i].get(newT).toString().length() != 0
                        && newField[i].get(newT) != String.valueOf(0)) {
                    newTail = i;
                }
            }
            //不取第一个自动增长字段
            for (int i = 1; i < newField.length; i++) {
                newField[i].setAccessible(true);
                if (newField[i].get(newT) != null
                        && newField[i].get(newT).toString().length() != 0
                        && newField[i].get(newT) != String.valueOf(0)) {

                    if (i == newTail) {

                        sql.append(String.format("\0 %s = '%s' ", newField[i].getName(), newField[i].get(newT)));
                    }
                    //第一个后面加 ,
                    else {

                        sql.append(String.format("\0 %s = '%s' , ", newField[i].getName(), newField[i].get(newT)));
                    }
                }
            }
            //获取条件字段
            //只获取自动增长字段
            oldField[0].setAccessible(true);
            sql.append(String.format("\0 WHERE 1 = 1 AND  %s = '%s'", oldField[0].getName(), oldField[0].get(oldT)));
        } catch (IllegalAccessException e) {
            System.out.println(String.format("获取修改对象的参数异常:%s", e.getMessage()));
            e.printStackTrace();
        }
        int i = SqlServerHelper.execUpdate(sql.toString(), "");

        return i;
    }

    /**
     * 根据对象新增保存
     * 首先判断对象在是否存在数据库 不存在就新增，
     *
     * @param t
     * @param <T>
     * @return
     */
    public <T> T save(T t) {
        Class clazz = t.getClass();
        Field[] fields = clazz.getDeclaredFields();
        StringBuffer sql = new StringBuffer();
        sql.append(String.format("INSERT INTO [%s] VALUES (", clazz.getSimpleName()));
        try {
            //插入策略，第一个不插入，都是自动增长，最后一个不要,号
            for (int i = 1; i < fields.length; i++) {
                fields[i].setAccessible(true);
                if (i == fields.length - 1) {
                    sql.append(String.format("'%s'", fields[i].get(t)));
                } else {
                    sql.append(String.format("'%s',", fields[i].get(t)));
                }

            }

        } catch (IllegalAccessException e) {
            System.out.println("获取新增对象参数异常：" + e.getMessage());
        }
        sql.append(")");

        int i = SqlServerHelper.execUpdate(sql.toString(), "");
        if (i <= 0) {
            return null;
        }


        return this.get(t);
    }

    /**
     * 根据对象删除
     *
     * @param t
     * @param
     * @return
     */
    public int del(T t) {
        Class clazz = t.getClass();
        Field[] fields = clazz.getDeclaredFields();
        StringBuffer sql = new StringBuffer();
        sql.append(String.format("DELETE [%s] WHERE 1 = 1 ", clazz.getSimpleName()));

        try {
            for (int i = 1; i < fields.length; i++) {
                fields[i].setAccessible(true);
                if (fields[i].get(t) != null && fields[i].get(t) != "") {
                    sql.append(String.format("\0 AND \0 %s = '%s'", fields[i].getName(), fields[i].get(t)));
                }
            }
        } catch (IllegalAccessException e) {
            System.out.println(String.format("获取删除对象参数异常：%s", e.getMessage()));
            e.printStackTrace();
        }
        int i = SqlServerHelper.execUpdate(sql.toString(), "");


        return i;
    }

    /**
     * 获取对象的集合+
     *
     * @return
     * @+p+aram t
     */
    public List<T> getList(T t) {
        //获取Class对象
        Class clazz = t.getClass();
        //获取对象成员的数组
        Field[] fields = clazz.getDeclaredFields();
        //将有值的成员添加进查询参数
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT  * FROM [")
                .append(clazz.getSimpleName())
                .append("]\0 WHERE 1 = 1");
        //参数集合
        ArrayList<Object> paramList = new ArrayList<>();
        try {
            for (Field field : fields) {
                field.setAccessible(true);//跳过语言检查
                if (field.get(t) != null && field.get(t) != "") {

                    sql.append("\0 AND\0")
                            .append(field.getName())
                            .append("=")
                            .append("\'")
                            .append("%s")
                            .append("\'");

                    //添加进集合
                    paramList.add(field.get(t));
                }
                ;

            }
        } catch (Exception e) {
            System.out.println("获取查询对象参数异常：" + e.getLocalizedMessage());
            e.printStackTrace();

        }
        //参数数组
        Object[] param = new Object[paramList.size()];
        for (int i = 0; i < paramList.size(); i++) {
            param[i] = paramList.get(i);
        }
        //执行sql
        List<Map> list = null;
        try {
            list = (List) SqlServerHelper.queryList(sql.toString(), param);
            if (list.size() == 0) {
                return null;
            }
        } catch (Exception e) {
            System.out.println(String.format("执行查询对象SQL失败%s", e.getMessage()));
        }
        List<T> listObj = new ArrayList<>();

        try {
            for (Map l : list) {
                Object obj = clazz.newInstance();
                for (Field field : fields) {

                    field.setAccessible(true);//路过语言查询
                    field.set(obj, l.get(field.getName())); //设置成员值

                }
                listObj.add((T) obj);
            }


        } catch (Exception e) {
            System.out.println(String.format("%s查询对象设置成员失败：%s", clazz.getName(), e.getMessage()));
            e.printStackTrace();
        }


        return listObj;
    }

    private boolean checkNull(Field field, T t) {
        boolean flag = false;
        field.setAccessible(true);
        try {
            if (field.get(t) == null
                    && field.get(t) == ""
                    && field.get(t).toString().length() == 0
                    && field.get(t).equals("null"
            )
            ) {
                flag = true;
            }

        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return flag;
    }
    private boolean checkNull(String s){
        boolean flag = false;
        if(s == null
        && s == ""
        && s.length() == 0
        && s.equals("null")
        && s != String.valueOf(0)){
            flag = true;
        }
        return  flag;

    }

    /**
     * 分页查询
     * @param page
     * @param size
     * @return
     */
    public List<T> queryPage(int page,int size,T t){
        Class clazz = t.getClass();
        Field[] fields = clazz.getDeclaredFields();
        StringBuilder builder = new StringBuilder();
        builder.append(String.format("SELECT * FROM [%s] WHERE 1 = 1 ",clazz.getSimpleName()));

        try{
            for(Field field:fields){
                field.setAccessible(true);
                if (field.get(t) != null
                        && !field.get(t) .equals("")
                        && !field.get(t).equals(0)
                        && !field.get(t).equals("null")) {
                    builder.append(String.format(" AND %s = '%s'",field.getName(),field.get(t)));
                }
            }
            builder.append(String.format(" ORDER BY %s OFFSET (%s * (%s-1)) ROW FETCH NEXT %s ROWS ONLY ",fields[0].getName(),size,page,size));
        }catch (Exception e){
            System.out.println("分页查询参数异常："+e.getMessage());
            e.printStackTrace();

        }
        //执行sql
        List<Map> list = null;
        try {
            list = (List)SqlServerHelper.queryList(builder.toString(),"");
            if(list.size()==0){
                return  null;
            }
        } catch (Exception e) {
            System.out.println("执行分页查询失败:"+e.getMessage());
            e.printStackTrace();
        }
        List<T> listObj = new ArrayList<>();

        try {
          for(Map map:list){
              Object obj = clazz.newInstance();
              for(Field field:fields){
                  field.setAccessible(true);

                  if ( map.get(field.getName()) != null
                          && ! map.get(field.getName()).equals("")
                          && ! map.get(field.getName()).equals(0)
                          && ! map.get(field.getName()).equals("null")) {
                        field.set(obj,map.get(field.getName()));
                  }
              }
              listObj.add((T)obj);
          }
        } catch (Exception e) {
            System.out.println("添加分页字段异常:"+e.getMessage());
            e.printStackTrace();
        }
        return listObj;

    }
    public PageModel<T> queryModel(int page,int size,T t){
        Class clazz = t.getClass();
        Field[] fields = clazz.getDeclaredFields();
        StringBuilder builder = new StringBuilder();
        builder.append(String.format("SELECT COUNT(1) FROM [%s] WHERE 1 = 1 ",clazz.getSimpleName()));

        try {
            for (Field field : fields) {
                field.setAccessible(true);
                if (field.get(t) != null
                        && !field.get(t).equals("")
                        && !field.get(t).equals(0)
                        && !field.get(t).equals("null")) {
                    builder.append(String.format(" AND %s = '%s'", field.getName(), field.get(t)));
                }
            }
        }catch (Exception e){
        System.out.println("分页模型参数异常："+e.getMessage());
        e.printStackTrace();
    }
        int totalSize  = (int)SqlServerHelper.querySingle(builder.toString());
        int totalPage;
        if(totalSize%size ==0){
            totalPage = totalSize / size;
        }else{
            totalPage = totalSize / size + 1;
        }
        PageModel model = new PageModel();
        model.setList(this.queryPage(page,size,t));
        model.setPage(page);
        model.setSize(size);
        model.setTotalPage(totalPage);
        model.setTotalSize(totalSize);
        return model;
    }
}




